﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_WA_GetStaffDutyTime]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_WA_GetStaffDutyTime];
GO
CREATE PROCEDURE [dbo].[sproc_WA_GetStaffDutyTime]
    @Staff_ID int,
    @day datetime
AS
/*
 * Gets the expected and actual duty time at the specified date.
 */
BEGIN
SET NOCOUNT ON

declare @onDutyTime datetime;
declare @offDutyTime datetime;
declare @actualOnDutyTime datetime;
declare @actualOffDutyTime datetime;
declare @dayValue int;
declare @r int;

-- Gets the expected duty time
exec @r = dbo.sproc_WA_GetDutyTime @staffid = @Staff_ID, @ondutytime = @onDutyTime output, @offdutytime = @offDutyTime output;
if ( @r <> 0 )
	return -1;  -- error?

set @actualOnDutyTime = null;
set @actualOffDutyTime = null;
set @dayValue = dbo.fn_GetDayValue(@day);
select
	@actualOnDutyTime = OnDuty,
	@actualOffDutyTime = OffDuty
from dbo.UDS_WorkAttendence_Data
where
	StaffID = @Staff_ID
	and DayValue = @dayValue;

-- Returns the data now
select
	@onDutyTime as OnDutyTime,
	@offDutyTime as OffDutyTime,
	@actualOnDutyTime as ActualOnDutyTime,
	@actualOffDutyTime as ActualOffDutyTime;

return 0;

END
GO